package com.intelligentmeterreadingsys.waterlist.reviewdata.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;
import com.intelligentmeterreadingsys.util.DateUtil;
import com.intelligentmeterreadingsys.util.Model;
import com.intelligentmeterreadingsys.util.SqlUtil;

import dbhelper.DbTableName;

/**
 * 抄表数据审核
 * 
 * @author king
 * 
 */
public class ReviewDataDao extends BaseDao {
	public ReviewDataDao(Context context) {
		super(context);
		this.db = super.getDb();
	}

	private SQLiteDatabase db;

	/**
	 * 查询一个复查信息
	 * 
	 * @param RFID
	 * @param waterid
	 * @return
	 */
	public UserWaterVO queryOneReview(String RFID, String waterid) {

		UserWaterVO water = null;
		String waternumber = null;
		String waternumberSql = "select w.[waternumber] from waterinfo w where 0=0";
		if (RFID != null) {
			waternumberSql = waternumberSql + "  and w.[RFIDnumber]='" + RFID
					+ "'";
		}
		if (waterid != null) {
			waternumberSql = waternumberSql + "  and w.[_id]=" + waterid;
		}
		Cursor c = db.rawQuery(waternumberSql, null);
		if (c.moveToNext()) {
			waternumber = c.getString(c.getColumnIndex("waternumber"));
		}
		if (c != null) {
			c.close();
		}
		if (waternumber == null || waternumber.equals("")) {
			return null;
		}

		String sql = "select w.[_id] as waterid, w.[waternumber],w.[usernumber],w.[wateradds], w.[lastmonthwater],w.[thismonthwater]"
				+ " ,w.[isupload],w.[metercaliber],u.[username],u.[phone],w.[iscopystatus],w.[alarmlimit],w.[lowalarmlimit] ,w.[waterproperties] "
				+ " from waterinfo w,userinfo u where w.[waternumber]='"
				+ waternumber
				+ "' and w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1' and u.[ismultiplecopy]='1'";

		System.out.println(sql);

		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			water = new UserWaterVO();
			water.setWateradds(cursor.getString(cursor
					.getColumnIndex("wateradds")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setMetercaliber(cursor.getString(cursor
					.getColumnIndex("metercaliber")));
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor
					.getColumnIndex("usernumber")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setAlarmlimit(cursor.getDouble(cursor
					.getColumnIndex("alarmlimit")));
			water.setLowalarmlimit(cursor.getDouble(cursor
					.getColumnIndex("lowalarmlimit")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			water.setWaterproperties(cursor.getString(cursor
					.getColumnIndex("waterproperties")));
			String thismonth = cursor.getString(cursor
					.getColumnIndex("thismonthwater"));
			if (thismonth != null && !thismonth.equals("")) {
				water.setThismonthwater(cursor.getDouble(cursor
						.getColumnIndex("thismonthwater")));
			} else {
				water.setThismonthwater(0.0);
			}
			water.setIsupload(cursor.getString(cursor
					.getColumnIndex("isupload")));
			water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
			water.setCopystatus(cursor.getString(cursor
					.getColumnIndex("iscopystatus")));

		}
		if (cursor != null) {
			cursor.close();
		}
		return water;

	}

	/**
	 * 修改本月的用水刻度
	 * 
	 * @param RFID
	 * @param thismonthwater
	 * @return
	 */
	public boolean saveWater(String waterid, Double thismonthwater,
			String desc, String isnorfid) {
		boolean b = false;
		if (waterid == null | waterid.equals("")) {
			return false;
		}
		ContentValues values = new ContentValues();
		if (thismonthwater != null) {
			values.put("thismonthwater", thismonthwater);
		}
		values.put("desc", desc);
		values.put("thisdate", DateUtil.getBeforeDay(0));
		values.put("iscopystatus", "已复抄");
		values.put("isnorfid", "0");

		int k = db.update(DbTableName.WATERINFO, values, "_id=?",
				new String[] { waterid });
		if (k > 0) {
			System.out.println("保存成功");
			b = true;

		}
		return b;

	}

	/**
	 * 已复抄数
	 * 
	 * @return
	 */
	public int queryReviewNumber() {
		int i = 0;
		String sql = "select _id from waterinfo where iscopystatus='已复抄' and ismultiplecopy='1'";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			i = cursor.getCount();
		}
		if (cursor != null) {
			cursor.close();
		}
		return i;

	}

	/**
	 * 所有需要复查数
	 * 
	 * @return
	 */
	public int queryAllReview() {
		int i = 0;
		String sql = "select _id from waterinfo where ismultiplecopy='1'";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			i = cursor.getCount();
		}
		if (cursor != null) {
			cursor.close();
		}
		return i;
	}

	/**
	 * 查询所有未上传信息
	 * 
	 * @return
	 */
	public List<WaterInfoVO> queryAllNoUpload(String waterid) {
		Cursor cursor = null;
		Cursor cursorimg = null;
		WaterInfoVO water = null;
		List<WaterInfoVO> list = new ArrayList<WaterInfoVO>();
		String sql = "select * from waterinfo where iscopystatus='已复抄' and isupload='0' and ismultiplecopy='1'";
		if (waterid != null) {
			sql = sql + " where _id=" + waterid;
		}
		cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			water = new WaterInfoVO();
			water.setThisdate(cursor.getString(cursor
					.getColumnIndex("thisdate")));
			water.setThismonthwater(cursor.getDouble(cursor
					.getColumnIndex("thismonthwater")));
			water.setId(cursor.getString(cursor.getColumnIndex("_id")));
			water.setSid(cursor.getString(cursor.getColumnIndex("sid")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setIsNorfid(cursor.getString(cursor
					.getColumnIndex("isnorfid")));
			String sql2 = "select * from " + DbTableName.RESOURCES
					+ " where otherid='" + water.getId()
					+ "' and type='1' and status='3'";
			cursorimg = db.rawQuery(sql2, null);

			List<String> imgpath = new ArrayList<String>();

			while (cursorimg.moveToNext()) {

				imgpath.add(cursorimg.getString(cursorimg
						.getColumnIndex("path")));
			}
			water.setImagepath(imgpath);
			list.add(water);
		}
		if (cursor != null) {
			cursor.close();
		}
		if (cursorimg != null) {
			cursorimg.close();
		}
		return list;

	};

	/**
	 * 所有已抄未上传复查数据
	 * 
	 * @return
	 */
	public int queryNoUpload() {
		int i = 0;
		String sql = "select _id from waterinfo where ismultiplecopy='1' and iscopystatus='已复抄' and isupload='0'";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			i = cursor.getCount();
		}
		if (cursor != null) {
			cursor.close();
		}
		return i;
	}

	/**
	 * 删除复抄数据
	 * 
	 * @return
	 */
	public void delectReivew() {
		db.delete(DbTableName.WATERINFO, "ismultiplecopy=?",
				new String[] { "1" });
		db.delete(DbTableName.RESOURCES, "status=?", new String[] { "3" });
		db.delete(DbTableName.USERINFO, "ismultiplecopy=?",
				new String[] { "1" });
		db.delete(DbTableName.USEWATERINFO, "ismultiplecopy=?",
				new String[] { "1" });
	}

	/**
	 * 查询复查数据
	 */
	public List<UserWaterVO> queryReviewlist(String username,
			String usernumber, String waterproperties, String wateradds,
			String waternumber, String copystatus) {
		StringBuffer sb = new StringBuffer();
		sb.append(
				"select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
				.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
				.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1'");

		if (username != null && !username.trim().equals("")) {

			sb.append(" and u.[username]='").append(username).append("'");
		}
		if (usernumber != null && !usernumber.trim().equals("")) {
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if (waterproperties != null && !waterproperties.trim().equals("")) {
			sb.append(" and w.[waterproperties]='").append(waterproperties)
					.append("'");
		}
		if (wateradds != null && !wateradds.trim().equals("")) {
			sb.append(" and w.[wateradds] like'%").append(wateradds)
					.append("%'");
		}
		if (waternumber != null && !waternumber.trim().equals("")) {
			sb.append(" and w.[waternumber]='").append(waternumber).append("'");
		}
		if (copystatus != null && !copystatus.trim().equals("")) {
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		String sql = sb.toString();
		System.out.println(sql);
		List<UserWaterVO> list = new ArrayList<UserWaterVO>();
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			UserWaterVO water = new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor
					.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor
					.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor
					.getColumnIndex("lastdate")));
			String iscopy = cursor.getString(cursor
					.getColumnIndex("iscopystatus"));
			if (iscopy.equals("已复查")) {
				water.setThismonthwater(cursor.getDouble(cursor
						.getColumnIndex("thismonthwater")));
			} else {
				water.setThismonthwater(0.0);
			}
			water.setCopystatus(cursor.getString(cursor
					.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor
					.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor
					.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor
					.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);
		}
		if (cursor != null) {
			cursor.close();
		}
		return list;
	}

	/**
	 * 设置成二次抄表
	 */

	public boolean Modifytowcopy(String number) {
		ContentValues value = new ContentValues();
		value.put("iscopystatus", "二次复抄");
		int id = db.update(DbTableName.WATERINFO, value,
				"waternumber=? and ismultiplecopy=?", new String[] { number,
						"1" });
		if (id > 0) {

			return true;
		}
		return false;
	}

	/**
	 * 查询所有示上传的复查数据
	 * 
	 * @param waterid
	 * @return
	 */
	public List<WaterInfoVO> queryReviewNoUpload(String waterid) {

		return null;

	}

	/**
	 * 查询所有未复抄的数据
	 * 
	 * @return
	 */
	public int queryNoCopyWater() {
		int i = 0;
		String sql = "select _id from waterinfo where iscopystatus='未复抄' or iscopystatus='二次复抄' and ismultiplecopy='1' ";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			i = cursor.getInt(cursor.getColumnIndex("_id"));
		}
		if (cursor != null) {
			cursor.close();
		}
		return i;
	}

	/**
	 * 通过水表编号查询未上传水表信息
	 * 
	 * @param waternumber
	 * @return
	 */
	public WaterInfoVO queryOneNoUpload(String waterid) {
		if (waterid == null || waterid.equals("")) {
			return null;
		}
		String sql = "select * from waterinfo where iscopystatus='已复抄' and isupload='0' and ismultiplecopy='1'";

		sql = sql + " and _id=" + waterid;

		Cursor cursor = db.rawQuery(sql, null);
		WaterInfoVO water = null;
		if (cursor.moveToNext()) {
			water = new WaterInfoVO();
			water.setThisdate(cursor.getString(cursor
					.getColumnIndex("thisdate")));
			water.setThismonthwater(cursor.getDouble(cursor
					.getColumnIndex("thismonthwater")));
			water.setSid(cursor.getString(cursor.getColumnIndex("sid")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			String lastwater = cursor.getString(cursor
					.getColumnIndex("lastmonthwater"));
			water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
			if (!TextUtils.isEmpty(lastwater)) {
				water.setLastmonthwater(Double.parseDouble(lastwater));
			} else {
				water.setLastmonthwater(0.0);
			}
			water.setIsNorfid(cursor.getString(cursor
					.getColumnIndex("isnorfid")));
			water.setId(cursor.getString(cursor.getColumnIndex("_id")));
		}

		if (water != null) {
			String sqlimgname = "select * from  " + DbTableName.RESOURCES
					+ " where otherid='" + water.getId()
					+ "' and type='1' and status='3'";

			Cursor c = db.rawQuery(sqlimgname, null);
			List<String> imgpath = new ArrayList<String>();
			while (c.moveToNext()) {

				imgpath.add(c.getString(c.getColumnIndex("path")));
			}
			if (c != null) {
				c.close();
			}
			if (cursor != null) {
				cursor.close();
			}
			water.setImagepath(imgpath);
		}
		return water;

	}

	/**
	 * 分页查询水表信息
	 * 
	 * @param model
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param waternumber
	 * @param bandingstatus
	 * @param copystatu
	 * @return
	 */
	public List<UserWaterVO> queryWaterlist(Model model, String usernumber,
			String username, String wateradds, String copystatus,
			String waternumber) {
		StringBuffer sb = new StringBuffer();
		sb.append(
				"select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
				.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
				.append(" from waterinfo w left join userinfo u on w.[usernumber]=u.[usernumber] where w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1'  ");

		if (usernumber != null && !usernumber.trim().equals("")) {
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if (username != null && !username.trim().equals("")) {
			sb.append(" and u.[username] like'%").append(username).append("%'");
		}
		if (waternumber != null && !waternumber.trim().equals("")) {
			sb.append(" and w.[wateradds] like'%").append(waternumber)
					.append("%'");
		}

		if (copystatus != null && !copystatus.trim().equals("")) {
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		if (waternumber != null && !waternumber.equals("")) {
			sb.append(" and w.[waternumber]='").append(waternumber).append("'");
		}
		sb.append(" group by waterid ");
		String sql = sb.toString();
		System.out.println(sql + "复抄SQL");
		Cursor cursor = SqlUtil.getAllItems(db,
				(model.getIndex() - 1) * model.getView_Count(),
				model.getView_Count(), sql);
		List<UserWaterVO> list = new ArrayList<UserWaterVO>();
		while (cursor.moveToNext()) {
			UserWaterVO water = new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor
					.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor
					.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor
					.getColumnIndex("lastdate")));
			String iscopy = cursor.getString(cursor
					.getColumnIndex("iscopystatus"));
			if (iscopy.equals("已复抄")) {
				water.setThismonthwater(cursor.getDouble(cursor
						.getColumnIndex("thismonthwater")));
			} else {
				water.setThismonthwater(0.0);
			}
			water.setCopystatus(cursor.getString(cursor
					.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor
					.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor
					.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor
					.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);
		}
		if (cursor != null) {
			cursor.close();
		}
		return list;

	}

	/**
	 * 得到总页数
	 * 
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param wateradds
	 * @param bandingstatus
	 * @param copystatus
	 * @return
	 */
	public int queryTotalPage(String usernumber, String username,
			String wateradds, String copystatus, String waternumber) {
		int page = 0;
		StringBuffer sb = new StringBuffer();
		sb.append("select w.[_id] as waterid")
				.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1' ");

		if (usernumber != null && !usernumber.trim().equals("")) {
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if (username != null && !username.trim().equals("")) {
			sb.append(" and u.[username] like'%").append(username).append("%'");
		}
		if (wateradds != null && !wateradds.trim().equals("")) {
			sb.append(" and w.[wateradds] like'%").append(wateradds)
					.append("%'");
		}

		if (copystatus != null && !copystatus.trim().equals("")) {
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		if (waternumber != null && !waternumber.equals("")) {
			sb.append(" and w.[waternumber]='").append(waternumber).append("'");
		}
		sb.append(" group by waterid ");
		String sql = sb.toString();

		System.out.println(sql);
		int k = super.getCount(sql);
		page = k % 10 > 0 ? k / 10 + 1 : k / 10;
		return page;

	}

	/**
	 * 查询复抄是否拍照
	 * 
	 * @param waterid
	 * @return
	 */
	public boolean isPhotograph(String waterid) {
		if (waterid == null || waterid.equals("")) {
			return false;
		}
		String sql = "select * from " + DbTableName.RESOURCES
				+ "  where status='3' and type='1' and otherid='" + waterid
				+ "'";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			return true;
		}
		return false;

	}
}
